﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using Liju.Model;

namespace LiJu.Db
{
    public class ProductRecordDbHelper
    {
        /// <summary>
        /// 查询需更新MES的产品记录
        /// </summary>
        /// <returns></returns>
        public static List<ProductRecordModel> GetProductRecordNeedUpdateMES()
        {
            string strMySql = "select * from productrecord where TaskStepNo=6 and UpdateTime<='"+
                DateTime.Now.AddMinutes(-1).ToString("yyyy-MM-dd HH:mm:ss")+"'";
            DataTable dt = DalMySQL.ExecuteDataTable(strMySql);
            var products = DbExtension.DataTableToList<ProductRecordModel>(dt);
            return products;
        }

        /// <summary>
        /// 查询所有产品记录
        /// </summary>
        /// <returns></returns>
        public static List<ProductRecordModel> GetAllProductRecords()
        {
            //获取没有删除的产品
            //string strMySql = "select * from productrecord where Status!=2  or Status is Null";
            string strMySql = "select * from productrecord order by UpdateTime desc";
            DataTable dt = DalMySQL.ExecuteDataTable(strMySql);
            var products = DbExtension.DataTableToList<ProductRecordModel>(dt);
            return products;
        }

        /// <summary>
        /// 查当日产量
        /// </summary>
        /// <returns></returns>
        public static int GetProductTodayYield()
        {
            //string strMySql = "select count(*) from productrecord where State=1 and UpdateTime >= '"+ DateTime.Now.Date.ToString("yyyy-MM-dd") +
            //    " 00:00:00' and UpdateTime <='" + DateTime.Now.Date.ToString("yyyy-MM-dd") + " 23:59:59'";
            //string strMySql = $"select count(*) from productrecord where State=1 and UpdateTime BETWEEN '{DateTime.Now.Date.ToString("yyyy-MM-dd")} 00:00:00' AND '{DateTime.Now.Date.ToString("yyyy-MM-dd")} 23:59:59'";
            //mod by zxm
            string strMySql = $@"select count(*) FROM(SELECT ProductSN FROM productrecord
            where State = 1 and UpdateTime BETWEEN '{DateTime.Now.Date.ToString("yyyy-MM-dd")} 00:00:00' 
                                               and '{DateTime.Now.Date.ToString("yyyy-MM-dd")} 23:59:59'
            GROUP BY ProductSN) t ";

            DataTable dt = DalMySQL.ExecuteDataTable(strMySql);
            return Convert.ToInt32(dt.Rows[0][0]);
        }

        /// <summary>
        /// 根据条码查询产品生产记录
        /// </summary>
        /// <returns></returns>
        public static ProductRecordModel GetProductRecordInfo(string strCode)
        {
            string strMySql = $"select * from ProductRecord where ProductSN='{strCode}' order by ProductRecordID desc";
            DataTable dt = DalMySQL.ExecuteDataTable(strMySql);
            var products = DbExtension.DataTableToList<ProductRecordModel>(dt);

            return products.FirstOrDefault();
        }

        /// <summary>
        /// 添加产品生产记录 返回自增主键ID -1异常
        /// </summary>
        /// <returns></returns>
        public static Int64 ProductRecordAdd(ProductRecordModel productRecord)
        {
            string strThisTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
            string strMySql = "insert into ProductRecord (ProductID, ProductName, ProductSN, TaskNum, TaskStepNo, ImgNum, OrderSN, OrderNun, "+
                "Model, WorkNo, State, NGReMark, GongHao, UserName, CreateTime, UpdateTime) values(" + productRecord.ProductId + ",'"+
                productRecord.ProductName + "','" + productRecord.ProductSN + "'," + productRecord.TaskNum + "," + productRecord.TaskStepNo + "," + 
                productRecord.ImgNum + ",'" + productRecord.OrderSN + "'," + productRecord.OrderNun + ",'" + productRecord.Model + "'," + 
                productRecord.WorkNo + "," + productRecord.State + ",'" + productRecord.NGReMark + "','" + productRecord.GongHao + "','" +
                productRecord.UserName + "','" + strThisTime + "','" + strThisTime + "'); select @@IDENTITY ";
            DataTable dt = DalMySQL.ExecuteDataTable(strMySql);
            if (dt != null && dt.Rows.Count > 0)
            {
                return Convert.ToInt64(dt.Rows[0][0]);
            }
            else
            {
                return -1;
            }
        }

        /// <summary>
        /// 修改产品生产记录 
        /// </summary>
        /// <returns></returns>
        public static int ProductRecordUpdate(ProductRecordModel productRecord)
        {
            string strMySql = "update ProductRecord set WorkNo=" + productRecord.WorkNo + ", TaskStepNo=" + productRecord.TaskStepNo +
                ", State=" + productRecord.State + ", GongHao='" + productRecord.GongHao + "', UserName='" + productRecord.UserName +
                "', TaskStepScrewNum="+ productRecord.TaskStepScrewNum + ", UpdateTime='" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") +
                "',NGReMark='"+ productRecord.NGReMark + "' where ProductRecordID= " + productRecord.ProductRecordID;
            return DalMySQL.ExecuteNonQuery(strMySql);
        }

        /// <summary>
        /// 修改产品生产记录状态 写MES完成 
        /// </summary>
        /// <returns></returns>
        public static int ProductRecordMESUpdate(ProductRecordModel productRecord)
        {
            string strMySql = "update ProductRecord set TaskStepNo=" + productRecord.TaskStepNo + ", UpdateTime='" + 
                DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "' where ProductSN= '" + productRecord.ProductSN + "'";
            return DalMySQL.ExecuteNonQuery(strMySql);
        }
    }
}
